/*DONE* Table [dbo].[<CompanyName>$Transfer Line$437dbf0e-84ff-417a-965d-ed2bb9650972] 1/2  Script Date: 14.1.2021 - JI ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$Transfer Line$437dbf0e-84ff-417a-965d-ed2bb9650972]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$Transfer Line$437dbf0e-84ff-417a-965d-ed2bb9650972](
	[timestamp] [timestamp] NOT NULL,
	[Document No_] [nvarchar](20) NOT NULL,
	[Line No_] [int] NOT NULL,
	[Item No_] [nvarchar](20) NOT NULL,
	[Quantity] [decimal](38, 20) NOT NULL,
	[Unit of Measure] [nvarchar](50) NOT NULL,
	[Qty_ to Ship] [decimal](38, 20) NOT NULL,
	[Qty_ to Receive] [decimal](38, 20) NOT NULL,
	[Quantity Shipped] [decimal](38, 20) NOT NULL,
	[Quantity Received] [decimal](38, 20) NOT NULL,
	[Status] [int] NOT NULL,
	[Shortcut Dimension 1 Code] [nvarchar](20) NOT NULL,
	[Shortcut Dimension 2 Code] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Gen_ Prod_ Posting Group] [nvarchar](20) NOT NULL,
	[Inventory Posting Group] [nvarchar](20) NOT NULL,
	[Quantity (Base)] [decimal](38, 20) NOT NULL,
	[Outstanding Qty_ (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ to Ship (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ Shipped (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ to Receive (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ Received (Base)] [decimal](38, 20) NOT NULL,
	[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
	[Unit of Measure Code] [nvarchar](10) NOT NULL,
	[Outstanding Quantity] [decimal](38, 20) NOT NULL,
	[Gross Weight] [decimal](38, 20) NOT NULL,
	[Net Weight] [decimal](38, 20) NOT NULL,
	[Unit Volume] [decimal](38, 20) NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Units per Parcel] [decimal](38, 20) NOT NULL,
	[Description 2] [nvarchar](50) NOT NULL,
	[In-Transit Code] [nvarchar](10) NOT NULL,
	[Qty_ in Transit] [decimal](38, 20) NOT NULL,
	[Qty_ in Transit (Base)] [decimal](38, 20) NOT NULL,
	[Transfer-from Code] [nvarchar](10) NOT NULL,
	[Transfer-to Code] [nvarchar](10) NOT NULL,
	[Shipment Date] [datetime] NOT NULL,
	[Receipt Date] [datetime] NOT NULL,
	[Derived From Line No_] [int] NOT NULL,
	[Shipping Agent Code] [nvarchar](10) NOT NULL,
	[Shipping Agent Service Code] [nvarchar](10) NOT NULL,
	[Appl_-to Item Entry] [int] NOT NULL,
	[Shipping Time] [varchar](32) NOT NULL,
	[Direct Transfer] [tinyint] NOT NULL,
	[Dimension Set ID] [int] NOT NULL,
	[Item Category Code] [nvarchar](20) NOT NULL,
	[Product Group Code] [nvarchar](10) NOT NULL,
	[Completely Shipped] [tinyint] NOT NULL,
	[Completely Received] [tinyint] NOT NULL,
	[Outbound Whse_ Handling Time] [varchar](32) NOT NULL,
	[Inbound Whse_ Handling Time] [varchar](32) NOT NULL,
	[Transfer-from Bin Code] [nvarchar](20) NOT NULL,
	[Transfer-To Bin Code] [nvarchar](20) NOT NULL,
	[Planning Flexibility] [int] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$Transfer Line$437dbf0e-84ff-417a-965d-ed2bb9650972$Key1] PRIMARY KEY CLUSTERED 
(
	[Document No_] ASC,
	[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$Transfer Line$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
